package util

import groovy.sql.Sql

import org.apache.commons.dbcp2.BasicDataSource
import org.slf4j.Logger
import org.slf4j.LoggerFactory

class DbUtil {
	private static final Logger log = LoggerFactory.getLogger(DbUtil.class)
	static Sql getSql() {
		def ds = new BasicDataSource()
		ds.with {
			driverClassName = 'com.mysql.cj.jdbc.Driver'
			username = 'root'
			password = 'root'
			url = 'jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false'
		}
		return new Sql(ds)
	}
	static prepare() {
		log.info('preparing database ...')

		def ddls = [
			'''drop table if exists t_athlete;
''',
			'''create table if not exists t_athlete (
id int primary key auto_increment,
firstname varchar(100),
lastname varchar(100),
birthday date
) engine innodb default charset utf8;

''', '''
insert into t_athlete (firstname, lastname, birthday) 
values('zhao', 'bo', '1986-05-29');
''', '''
insert into t_athlete (firstname, lastname, birthday) 
values('d', 'jh', '1988-03-22');
''', '''
insert into t_athlete (firstname, lastname, birthday) 
values('b', 'b', '2017-11-11');
'''
		];
		def sql = getSql()
		ddls.each {
			sql.execute(it)
		}
		sql.close()
		log.info('done.')
	}
}
